﻿# -*- coding: utf-8 -*-
"""
	sjtu-mailing-list
	~~~~~~~~~~~~~~~~~

	:copyright: (c) 2010 by sjtu-mailing-list-group.
	:author: pcj
	:license: BSD, see LICENSE for more details.
"""
from __future__ import with_statement
from sqlite3 import dbapi2 as sqlite3
from contextlib import closing
from flask import Flask, request, session, g, redirect, url_for, abort, \
	render_template, flash,jsonify
import simplejson
from flaskext.mail import Mail, Message

# configuration
DATABASE = 'sml.db'
DEBUG = True
SECRET_KEY = 'development key'
USERNAME = 'admin'
PASSWORD = 'default'

API_ROOT = "http://localhost:5000"

# create our little application :)
app = Flask(__name__)
app.config.from_object(__name__)
app.config.from_envvar('SJTUMAIL_SETTINGS', silent=True)
mail = Mail(app)

def connect_db():
	"""Returns a new connection to the database."""
	return sqlite3.connect(app.config['DATABASE'])


def init_db():
	"""Creates the database tables."""
	with closing(connect_db()) as db:
		with app.open_resource('schema.sql') as f:
			db.cursor().executescript(f.read())
		db.commit()


@app.before_request
def before_request():
	"""Make sure we are connected to the database each request."""
	g.db = connect_db()


@app.after_request
def after_request(response):
	"""Closes the database again at the end of the request."""
	g.db.close()
	return response


@app.route('/')
def show_home():
	#cur = g.db.execute('select title, text from entries order by id desc')
	#entries = [dict(title=row[0], text=row[1]) for row in cur.fetchall()]
	#return render_template('home.html', user_id=session['userid'])
	if not session.get('logged_in'):
		return render_template('login.html')
	else:
		return render_template('home.html', user_id=session['userid'])


@app.route('/add', methods=['POST'])
def add_entry():
	if not session.get('logged_in'):
		abort(401)
	g.db.execute('insert into entries (title, text) values (?, ?)',
			[request.form['title'], request.form['text']])
	g.db.commit()
	flash('New entry was successfully posted')
	return redirect(url_for('show_entries'))


@app.route('/login', methods=['GET', 'POST'])
def login():
	error = None
	#print request.args['username']
	password_dict=query_db('select password from JXUser where name==?',[request.args['username']],one=True)
	#password_dict=query_db('select * from JXUser',one=True)
	#print password_dict
	password=unicode(password_dict['password'])
	if password == None:
		error = 'Invalid username'
		return error
	elif request.args['password'] != password:
		error = 'Invalid password'
		return error
	else:
		session['logged_in'] = True
		session['username']=request.args['username']
		id_dict=query_db('select id from JXUser where name ==?',[request.args['username']],one=True)
		session['userid']=id_dict['id']
		flash('You were logged in')
		return redirect(url_for('show_home'))


@app.route('/logout')
def logout():
	session.pop('logged_in', None)
	session.pop('username',None)
	session.pop('userid',None)
	flash('You were logged out')
	return render_template('login.html')



@app.route('/group/getInfo', methods=['POST','GET'])
def getGroupInfo():
	group_id=request.args['groupid']
	policy=query_db('select policy from JXGroup where id==?', [group_id], one=True)
	if policy['policy']:
		flash('It is a private group')
		return 'not the public group'
	ls = query_db('select * from JXGroup where id==?', [group_id],one=True)
	return jsonify(ls)

@app.route('/group/getPublicList', methods=['POST','GET'])
def getPublicList():
	#error='get public list error'
	group_as_ls = query_db('select * from JXGroup where policy==0')
	return simplejson.dumps(group_as_ls)

@app.route('/group/member/getInfo', methods=['POST','GET'])
def getGroupMemberInfo():
	#if session.get('logged_in'):
	#	abort(401)
	#address_id=request.args['address_id']
	username=request.args['username']
	#if not username == "" :
	return jsonify(query_db('select id,name,fullname,info,receive_by,address from JXUser where name==?', [username],one=True))
	#return jsonify(query_db('select * from JXUser where address_id==?', [address_id]))

@app.route('/group/member/getList', methods=['POST','GET'])
def listMemberByGroupId():
	print 'sdsdsdsds'
	if not session.get('logged_in'):
		abort(401)
	group_id=request.args['groupid']
	policy=query_db('select policy from JXGroup where id==?', [group_id], one=True)
	if policy['policy']:
		user_as_ls = query_db('select * from JXUser where JXUser.id in (select user_id from JXUser_Group where JXUser_Group.group_id==?)', [group_id])
		return simplejson.dumps(user_as_ls)
	elif not checkpower_owner(session['userid']):
		return "have no rights"
	else: 
		user_as_ls = query_db('select * from JXUser where JXUser.id in (select user_id from JXUser_Group where JXUser_Group.group_id==?)', [group_id])
		print user_as_ls
		return simplejson.dumps(user_as_ls)

@app.route('/group/member/add', methods=['POST','GET'])
def addGroupMember():
	if not session.get('logged_in'):
		abort(401)
	address=request.args['address']
	group_id=request.args['groupid']
	if not checkpower_admin(session['userid']):
		return 'have no rights'
	#address_id = query('select id from JXAddress where address==?', [address], one=True)
	user_id = query_db('select id from JXUser where address==?', [address], one=True)
	query_db('insert into JXUser_Group (user_id,group_id,power) values (?,?,?)', [user_id['id'], group_id, 0])
	query_db('insert into JXGroup_Address (group_id,address) values (?,?)', [group_id, address])
	g.db.commit()
	return simplejson.dumps(query_db('select * from JXUser where id==?', [user_id['id']]))

@app.route('/group/member/remove', methods=['POST','GET'])
def removeGroupMember():
	if not session.get('logged_in'):
		abort(401)
	group_id=request.args['groupid'] 
	address=request.args['address']
	if not checkpower_admin(session['userid']):
		return 'The address cannot be deleted.'
	user_id = (query_db('select id from JXUser where address==?', [address], one=True))['id']
	query_db('delete from JXGroup_Address where address==? and group_id==?', [address, group_id])
	query_db('delete from JXUser_Group where user_id==? and group_id==?', [user_id, group_id])
	g.db.commit()
	return 'remove successfully'


@app.route('/usr/getInfo', methods=['GET','POST'])
def getUserInfo():
	user_id=None
	username=None
	address=None
	try:
		user_id=request.args['userid']
	except:pass
	try:
		username=request.args['username']
	except:pass
	try:
		address=request.args['address']
	except:pass
	if user_id == None and username == None and address == None:
		print 'no arguments'
		return 'no arguments'
	if user_id != None:
		return jsonify(query_db('select id,name,fullname,info,receive_by,address from JXUser where id==?', [user_id],one=True))
	if username != None:
		return jsonify(query_db('select id,name,fullname,info,receive_by,address from JXUser where id==?', [user_id],one=True))
	if address != None:
		return jsonify(query_db('select id,name,fullname,info,receive_by,address from JXUser where address==?', [address],one=True))


@app.route('/user/modify', methods=['POST'])
def modifyUser():
	if not session.get('logged_in'):
		abort(401)
	address=username=fullname=password=None
	try:
		address=request.args['address']
	except:pass
	try:
		username=request.args['username']
	except:pass
	try:
		fullname=request.args['fullname']
	except:pass
	try:
		password=request.args['password']
	except:pass
	currentuser_id=session['userid']
	if not address == None:
		query_db('update JXUser set address=? where id==? ', [address, currentuser_id])
	if not username == None:
		query_db('update JXUser set username=? where id==? ', [username, currentuser_id])
	if not fullname == None:
		query_db('update JXUser set fullname=? where id==? ', [fullname, currentuser_id])
	if not password == None:
		query_db('update JXUser set password=? where id==? ', [password, currentuser_id])
	g.db.commit()
	return jsonify(query_db('select * from JXUser where id==? ', [currentuser_id],one=True))

@app.route('/user/register', methods=['GET','POST'])
def registerUser():
	address = request.args['address']
	passwd = request.args['passwd']
	name = request.args['name']
	fullname = request.args['fullname']
	query_db('insert into JXUser (address,password,name,fullname) values (?,?,?,?)', [address,passwd, name, fullname])   
	g.db.commit()
	user_id = query_db('select id from JXUser where name==?', [name],one=True)
	#return jsonify(query_db('select * from JXUser where name==?', [name],one=True))
	session['logged_in'] = True
	session['username'] = name
	session['userid'] = user_id['id']
	flash('You were logged in')
	return redirect(url_for('show_home'))

#class user

@app.route('/group/getList', methods=['POST','GET'])
def listGroupByUserId():
	#if not session.get('logged_in'):
	#	abort(401)
	JXUser_id=request.args['ownerid']
	#page=request.args['page']
	groups_as_ls = query_db('select * from JXGroup where owner==(select JXUser.name from JXUser where JXUser.id==?) order by JXGroup.id', [JXUser_id])
	return simplejson.dumps(groups_as_ls)


@app.route('/group/getListn', methods=['GET','POST'])
def listGroupByUserName():
	JXOwner_name=request.args['ownername']
	return simplejson.dumps(query_db('select * from JXGroup where owner==?', [JXOwner_name]))



@app.route('/group/add', methods=['POST'])
def addGroup():
	if not session.get('logged_in'):
		abort(401)
	name=request.args['name']
	topic=request.args['topic']
	policy=request.args['policy']
	user_id=session['userid']
	username=session['username']
	query_db('insert into JXGroup (name,topic,policy,owner) values (?,?,?,? )', [name, topic, policy, username])
	groupid=(query_db('select id from JXGroup where name==?', [name],one=True))['id']
	query_db('insert into JXUser_Group (user_id,group_id,power) values (?,?,?)', [user_id, groupid, 0])
	#flash('add group sucessfully')
	g.db.commit()
	return jsonify(query_db('select * from JXGroup where name==?',[name],one=True))
	#return redirect(url_for('listGroupInfo', Group_id=groupid))
	

@app.route('/group/remove',methods=['POST'])
def removeGroupById():
	if not session.get('logged_in'):
		abort(401)
	JXGroup_id=request.args['groupid']
	user_id=session['userid']
	if not checkpower_owner(JXGroup_id):
		return "The group cannot be deleted"
	query_db('delete from JXGroup where id==?', [JXGroup_id])
	query_db('delete from JXUser_Group where group_id==?',[JXGroup_id])
	query_db('delete from JXGroup_Address where group_id==?',[JXGroup_id])
	query_db('delete from JXGroup_Mail where group_id==?',[JXGroup_id])
	g.db.commit()
	print ('remove Groupid: ' + JXGroup_id)
		   #redirect(url_for('listGroupUserId'))
	return ('remove Groupid: ' + JXGroup_id)

#@app.route('/updategroup',methods=['POST'])
#	def UpdateGroupById(JXGroup_id):
#		g.db.commit()

@app.route('/group/modify', methods=['POST','GET'])
def ModifyGroupById():
	if not session.get('logged_in'):
		abort(401)
	name=topic=policy=owner=None
	user_id=session['userid']
	JXGroup_id=request.args['groupid']
	try:
		name=request.args['name']
	except:pass
	try:
		topic=request.args['topic']
	except:pass
	try:
		policy=request.args['policy']
	except:pass
	try:
		owner=request.args['owner']
	except:pass
	print JXGroup_id
	if not checkpower_owner(JXGroup_id):
		print "No rights to modify"
		return "No rights to modify"
	if not name == None:
		query_db('update JXGroup set name=? where id==? ', [name, JXGroup_id])
	if not topic == None:
		query_db('update JXGroup set topic=? where id==? ', [topic, JXGroup_id])
	if not policy == None:
		query_db('update JXGroup set policy=? where id==? ', [policy, JXGroup_id])
	if not owner == None:
		query_db('update JXGroup set owner=? where id==? ', [owner, JXGroup_id])
	g.db.commit()
	return jsonify(query_db('select * from JXGroup where id==? ', [JXGroup_id],one=True))



#class group
@app.route('/address/listmailaddress', methods=['POST', 'GET'])
def listAddressByGroupId():
	print 'chuanjinqu'
	if not session.get('logged_in'):
		abort(401)
	user_id=session['userid']
	JXGroup_id=request.args['groupid']
	policy=query_db('select policy from JXGroup where id==?', [JXGroup_id], one=True)
	if not policy['policy']:
		address_as_ls = query_db('select address from JXUser where JXUser.id in (select user_id from JXUser_Group where JXUser_Group.group_id==?)', [JXGroup_id])
		return simplejson.dumps(address_as_ls)
	elif not checkpower_owner(JXGroup_id):
		print 'have no rights'
		return "have no rights"
	else: 
		address_as_ls = query_db('select address from JXUser where JXUser.id in (select user_id from JXUser_Group where JXUser_Group.group_id==?)', [JXGroup_id])
		return simplejson.dumps(address_as_ls)

@app.route('/address/listmailaddressn', methods=['POST', 'GET'])
def listAddressByGroupName():
	if not session.get('logged_in'):
		abort(401)
	user_id=session['userid']
	JXGroup_name=request.args['groupname']
	JXGroup_id=(query_db('select id from JXGroup where name==?',[JXGroup_name],one=True))['id']
	policy=query_db('select policy from JXGroup where id==?', [JXGroup_id], one=True)
	if not policy['policy']:
		address_as_ls = query_db('select address from JXGroup_Address where group_id==?', [JXGroup_id])
		return simplejson.dumps(address_as_ls)
	elif not checkpower_owner(JXGroup_id):
		print 'have no rights'
		return "have no rights"
	else: 
		address_as_ls =query_db('select address from JXGroup_Address where group_id==?', [JXGroup_id])
		return simplejson.dumps(address_as_ls)

'''
@app.route('/addmailaddress', methods=['POST'])
def addAddressByGroupId():
	if not session.get('logged_in'):
		abort(401)
	JXGroup_id=request.args['groupid']
	query_db('insert into JXAddress (address) values(?)', [data.address])
	address_id = query_db('select id from JXAddress where address==', [data.address])
	query_db('insert into JXGroup_Address (group_id,address_id) values (?,?)', [address_id, JXGroup_id])
	flash('add address successfully')
	return redirect(url_for('listMailAddressByGroupId'))

@app.route('/removemailaddress', methods=['POST'])
def removeMailAddressById():
	if not checkpower_admin():
		abort(401) 
	JXAddress_id=request.args[addressid]
	query_db('delete from JXGroup_Address where address_id==?', [JXAddress_id])
	flash('remove the mail sucessfully')
	return redirect(url_for('listMailAddressByGroupId'))
'''



@app.route('/mail/getList', methods=['GET','POST'])
def listMailByGroupId():
	if not session.get('logged_in'):
		abort(401)
	JXGroup_id=request.args['groupid']
	#print JXGroup_id
	policy=query_db('select policy from JXGroup where id==?', [JXGroup_id], one=True)
	if not policy['policy']:
		mails_as_ls = query_db('select * from JXMail where id in (select mail_id from JXGroup_Mail where group_id==?)', [JXGroup_id])
		return  simplejson.dumps(mails_as_ls)
	elif not checkpower_owner(JXGroup_id):
		return "have no rights"
	else: 
		mails_as_ls = query_db('select * from JXMail where id in (select mail_id from JXGroup_Mail where group_id==?)', [JXGroup_id])
		return  simplejson.dumps(mails_as_ls)

@app.route('/mail/getListn', methods=['POST', 'GET'])
def listMailByGroupName():
	JXGroup_name=request.args['groupname']
	JXGroup_id=(query_db('select id from JXGroup where name==?',[JXGroup_name],one=True))['id']
	return redirect(url_for('listMailByGroupId',groupid=JXGroup_id))







# def JXRemoveMailByName(JXMail_name):
#	g.db.execute('delete from JXMail where JXMail_name')


@app.route('/mail/remove', methods=['POST','GET'])
def JXRemoveMailById():
	JXMail_id=request.args['mailid']
	#JXGroup_id=request.args['groupid']
	if not session.get('logged_in'):
		abort(401)
	#if not checkpower_admin(JXGroup_id):
	#	return "have no rights"
	query_db('delete from JXMail where id==?', [JXMail_id])
	query_db('delete from JXGroup_Mail where mail_id==?',[JXMail_id])
	#flash('remove mail successfully')
	g.db.commit()
	#return redirect(url_for('listMailByGroupId'))
	return "delete sucessfully"

'''
#class mail
@app.route('/mail', methods=['POST'])
def JXStarMailById():
	JXM
	if not session.get('logged_in'):
		abort(401)
	query_db('update JXMail set star=1 where id==?', [JXMail_id])   
	return redirect(url_for('listMailByGroupId'))

# def JXStarMailByName(JXMail_name):
'''

@app.route('/mail/sendmail',methods=['POST','GET'])
def JXSendMail():
	try: 
		with mail.connect() as conn:
			message = request.args['content']
			subject = request.args['subject'] 
			#for receiver in data.receiver:
			receiver=request.form['receiver']
			msg = Message(recipients=[receiver],
					body=message,
					subject=subject)
			msg.sender = request.args['sender']
			conn.send(msg)
	except Exception, e:
		print str(e)
		print 'error'
		return "error"
	print 'sssssss'
	return 'sucessful'
	#sender=request.form[]
	#query_db('insert into JXMail (sender,recipient,timestamp,zone,content) values (?,?,?,?,?)', [sender, receiver, timestamp, data.zone, data.content])

@app.route('/mail/sendtogroup', methods=['POST','GET'])
def sendmail_to_group():
	JXGroup_id=request.args['groupid']
	sender=request.args['sender']
	content=request.args['content']
	subject=request.args['subject']
	#receiver_json =redirect(url_for('listAddressByGroupId',groupid=JXGroup_id))
	receiver_ls=query_db('select address from JXGroup_address where group_id==?',[JXGroup_id])
	print receiver_ls
	#receiver_dict = simplejson.loads(listMailByGroupId(JXGroup_id))
	#mailmsg_dict = {'sender':sender,
	#		'subject' : subject,
	#		'content' : content,
	#		'receiver' : receiver_dict}
	try: 
		with mail.connect() as conn:
			message = content
			subject = subject
			#for receiver in data.receiver:
			for receiver in receiver_ls:
				#receiver=request.form['receiver']
				msg = Message(recipients=[receiver['address']],
					body=message,
					subject=subject)
				msg.sender = request.args['sender']
				conn.send(msg)
				#query_db('insert into JXMail (sender,recipient,timestamp,zone,content) values (?,?,?,?,?)', [sender, receiver['address'], , 8, content]')
	except Exception, e:
		print str(e)
		print 'error'
		return "error"
	return 'sucessful'
		#redirect(url_for('JXsendMail',sender=sender,content=content,subject=subject,receiver=receiver['address']))

##### addition ####

def checkpower_admin(JXgroup_id):
	return (query_db('select power from JXUser_Group where user_id==? and group_id==?', [session['userid'], JXgroup_id], one=True))['power'] <= 1


def checkpower_owner(JXgroup_id):
	return (query_db('select power from JXUser_Group where user_id==? and group_id==?', [session['userid'], JXgroup_id], one=True))['power'] == 0

def query_db(query, args=(), one=False):
	cur = g.db.execute(query, args)
	rv = [dict((cur.description[idx][0], value)
		for idx, value in enumerate(row)) for row in cur.fetchall()]
	return (rv[0] if rv else None) if one else rv




if __name__ == '__main__':
	app.run(host='0.0.0.0', debug=True)
